package cn.chainco.cts.idaoImpl;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import cn.chainco.cts.idao.IUserDao;
import cn.chainco.cts.jdo.Company;
import cn.chainco.cts.jdo.Employee;
import cn.chainco.cts.jdo.User;
import cn.chainco.cts.jdo.Work;

public class UserDaoImpl extends GenericHibernateDaoImpl<User, Long> implements IUserDao{
	
	@SuppressWarnings("unchecked")
	public User findByNameAndPass(final String name , final String pwd){
		List<User> list = this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session _session) throws HibernateException, SQLException{
				Query query = _session.createQuery("from User u where u.userName = ?  and u.userPass = ?");
				query.setParameter(0, name);
				query.setParameter(1, pwd);
				return query.list();
			}
		});
		
		if(list.size() > 0){
			return list.get(0);
		}else{
			return null;
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<User> findByName(final String name){
	  String sql = " select * from cts_user where 1=1 and userName like '%"+name+"%' and status=0 ";
	  List<User> userlist = this.getSession().createSQLQuery(sql).addEntity(User.class).list();
	  return userlist;
		
	}
	
	@SuppressWarnings("unchecked")
	public <E>List<E> findSqlByElist(String sql , Class className, Map<String , String> mp){
		List<E> eList= this.getSession().createSQLQuery(sql).addEntity(className).list();
		return eList;
	}
	
	@SuppressWarnings("unchecked")
	public List<User> findUser(String userName , String createTime , Integer status , Map<String , String> searchCond){
		 String sql = " select * from cts_user where 1=1";
		
		 if(userName!=null && userName!=""){
			 sql += " and userName like '%"+userName+"%'";
		 }
		 
		 if(createTime!=null && createTime!=""){
			 sql += " and createTime='"+createTime+"'";
		 }
		 
		 if(status!=null){
		  	 sql += " and status="+status;
		 }
		 
		 sql += " order by createTime desc";
		 List<User> userlist = this.getSession().createSQLQuery(sql).addEntity(User.class).list();
		 return userlist;
	}
	
	/**
	 * 删除用户和相关连的数据
	 * @param company
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int delUserByAll(User user){
		int code = 0;
		try {
			String fuserSql = "select * from cts_user where 1=1 and id="+user.getId();
			List<User> userlist = this.getSession().createSQLQuery(fuserSql).addEntity(User.class).list();
			if(userlist.size()>0){
			   String fcompanySql = "select * from cts_company where 1=1 and userId="+user.getId();
			   List<Company> companylist = this.getSession().createSQLQuery(fcompanySql).addEntity(Company.class).list();	
			   if(companylist.size() > 0){
				    Company company = companylist.get(0);
				   
				    String workmSql = "delete from cts_work where companyId="+company.getId();
					String dcSql = "delete from cts_doctemplate where companyId="+company.getId();
					String feeSql = "delete from cts_fee where companyId="+company.getId();
					String itemSql = "delete from cts_item where companyId="+company.getId();
					String tagmSql = "delete from cts_tag where companyId="+company.getId();
					String emplSql = "delete from cts_employee where companyId="+company.getId();
					String companySql = "delete from cts_company where id="+company.getId();
					
					// WorkDoc
					String femplSql = "from Employee where 1=1 and company.id="+company.getId() + " and user="+user.getId();
					List<Employee> employee = this.findEList(femplSql);
					if(employee!=null){
						for (Employee employee2 : employee) {
							String workItemSql = "delete from cts_workdoc where employeeId="+employee2.getId();
							delexecureDel(workItemSql);
						}
					}
					
					String fWorkSql = "from Work where company.id="+company.getId();
					List<Work> worklist = this.findEList(fWorkSql);
					if(worklist!=null){
                        // WorkItem
						for (Work work3 : worklist) {
							String workDocSql = "delete from cts_workitem where workId="+work3.getId();
							delexecureDel(workDocSql);
						}
						
						// WorkFee
						for (Work work3 : worklist) {
							String workFeeSql = "delete from cts_workfee where workId="+work3.getId();
							delexecureDel(workFeeSql);
						}
					}
					//Work
					
					delexecureDel(workmSql);
					delexecureDel(dcSql);
					delexecureDel(feeSql);
					delexecureDel(itemSql);
					delexecureDel(tagmSql);
					delexecureDel(emplSql);
					delexecureDel(companySql);
			   }
			   
			    User _user = userlist.get(0);
			    String imSql = "delete from cts_im where senderId="+_user.getId() + " or receiverId="+_user.getId();
				String emplSql = "delete from cts_employee where userId="+_user.getId();
				String userSql = "delete from cts_user where id="+userlist.get(0).getId();
				
				delexecureDel(imSql);
				delexecureDel(emplSql);
				delexecureDel(userSql);
			}
			
			
			code = 1;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return code;
	}
	
	private int delexecureDel(String sql){
	  this.getSession().createSQLQuery(sql).executeUpdate();
	  return 0;
   }
	
	private <E>List<E> findEList(String sql){
	  List<E> list = (List<E>)this.getSession().createQuery(sql).list();
      return list;
   }
}
